//-----------------------------------------------------------------------
// <copyright file="SqlDisconnectUsers.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY 
// KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
// </copyright>
// <author>Barry Dorrans</author>
// <email>barryd@charteris.com</email>
// <date>2004-06-07</date>
// <summary>Unit test cases for the SqlServer DisconnectUserProcesses 
// action.</summary>
//-----------------------------------------------------------------------

namespace Microsoft.Sdc.Tasks.Test
{
    using System;
    using System.IO;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using Microsoft.Sdc.Tasks.Configuration;
    using Microsoft.Sdc.Tasks;
    using Microsoft.Build.Framework;


    // We need these to create suitable users to add into SQL
    using Microsoft.Sdc.Tasks.Configuration.ActiveDirectory;
    using Microsoft.Sdc.Tasks.ActiveDirectory;

    // And we need these to impersonate users without adequate permissions in SQL to 
    // disconnect processes
    using System.Runtime.InteropServices;
    using System.Security.Principal;
    using System.Security.Permissions;

    // And we need these to check our results
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;

    /// <summary>
    /// Unit tests for Tasks.Sql.disconnectUserProcesses scenarios
    /// </summary>
    [TestClass]
    public class SqlDisconnectUsersTests
    {
        private string testDBName;

        [TestInitialize]
        public void InitBuildEngineInterface()
        {
            SetupTestDB();
        }

        public SqlDisconnectUsersTests()
        {           
        }

        private void SetupTestDB()
        {
            Guid guid = Guid.NewGuid();
            string tempGUID = guid.ToString();
            this.testDBName = tempGUID.Replace("-", "").Substring(0, 11);

            
            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI;");
            dbConnection.Open();

            SqlCommand setupTestDB = new
                SqlCommand("CREATE DATABASE \"" + this.testDBName + "\"", dbConnection);
            setupTestDB.ExecuteNonQuery();

            dbConnection.Close();
        }

        [TestCleanup]
        public void DeleteTestDB()
        {
            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI;");
            dbConnection.Open();

            SqlCommand setupTestDB = new
                SqlCommand("DROP DATABASE \"" + this.testDBName+"\"", dbConnection);
            setupTestDB.ExecuteNonQuery();

            dbConnection.Close();
        }


        /// <summary>
        /// Test set/get on each task parameter
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestProperties()
        {
            Tasks.Sql.DisconnectUsers task = new Tasks.Sql.DisconnectUsers();
            
            task.CommandTimeout = 10;
            if (task.CommandTimeout != 10)
                Assert.Fail("set/get commandtimeout failed");
            task.DatabaseName = "(dbName)";
            if (task.DatabaseName != "(dbName)")
                Assert.Fail("set/get database name failed");
            task.ServerName = "(server)";
            if (task.ServerName != "(server)")
                Assert.Fail("set/get server name failed");

        }

        /// <summary>
        /// Test disconnect task on the local database (no connection property set)
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        [ExpectedException(typeof(SqlException))]
        public void TestDisconnectUsersAgainstLocalServer()
        {
            Tasks.Sql.DisconnectUsers task = new Tasks.Sql.DisconnectUsers();
            DateTime loginTime;

            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog="+this.testDBName+";" +
                            "Integrated Security=SSPI;"+
                            "Application Name=testSPID"+
                            "Connection Timeout=5");
            dbConnection.Open();
            loginTime = GetCurrentProcessLoginTime(dbConnection);

            task.DatabaseName = this.testDBName;

            try 
            {
                task.Execute();
                if (GetCurrentProcessLoginTime(dbConnection) == loginTime)
                    Assert.Fail("Login still exists, with identical login time");
            }
            finally
            {
                if (dbConnection.State == ConnectionState.Open)
                     dbConnection.Close();
            }
          
        }

        /// <summary>
        /// Test disconnect task against a SQL named instance
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        
        [TestMethod]
        [ExpectedException(typeof(SqlException))]
        public void TestDisconnectUsersAgainstNamedServer()
        {
            Tasks.Sql.DisconnectUsers task = new Tasks.Sql.DisconnectUsers();
            DateTime loginTime;


            SqlConnection dbConnection = new SqlConnection("Data Source=" + System.Environment.MachineName + "; " +
                            "Initial Catalog="+this.testDBName+";" +
                            "Integrated Security=SSPI;" +
                            "Application Name=testSPID;" +
                            "Connection Timeout=5");
            dbConnection.Open();
            loginTime = GetCurrentProcessLoginTime(dbConnection);

            task.ServerName = System.Environment.MachineName;
            task.DatabaseName = this.testDBName;

            try
            {
                task.Execute();
                if (GetCurrentProcessLoginTime(dbConnection) == loginTime)
                    Assert.Fail("Login still exists, with identical login time");
            }
            finally
            {
                if (dbConnection.State == ConnectionState.Open)
                  dbConnection.Close();
            }
        }


        /// <summary>
        /// Test disconnect task against an unknown SQL named instance
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestDisconnectUsersAgainstBadServer()
        {
            Tasks.Sql.DisconnectUsers task = new Tasks.Sql.DisconnectUsers();
            Guid guid = Guid.NewGuid();
            string instanceName = guid.ToString();
            instanceName = System.Environment.MachineName + @"\" + instanceName.Replace("-", "").Substring(0, 11);
            // Now check the instance doesn't actually exist
            try
            {
                SqlConnection dbConnection = new SqlConnection("Data Source=" + instanceName + ";" +
                                "Initial Catalog=master;" +
                                "Integrated Security=SSPI;" +
                                "Connection Timeout=1");
                dbConnection.Open();
                dbConnection.Close();
                Assert.Fail("Random SQL instance name actually exists on this machine.");
            }
            catch (SqlException)
            {
                // Swallow 'em.
            }

            try
            {
                task.ServerName = instanceName;
                task.DatabaseName = this.testDBName;
                task.ConnectionTimeout = 1;

                task.Execute();
                Assert.Fail("Process kill on non-existant instance did not fail.");
            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine) task.BuildEngine).PreviousMessage;
                // Make sure the error message was the instance not found
                Assert.IsTrue(lastMessage.IndexOf("Shared Memory Provider: The system cannot find the file specified.") >= 0);
            }
        }

        /// <summary>
        /// Test disconnect task on the local database in the master database
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void TestDisconnectUsersAgainstMasterDatabase()
        {
            Tasks.Sql.DisconnectUsers task = new Tasks.Sql.DisconnectUsers();
            DateTime loginTime;

            SqlConnection dbConnection = new SqlConnection("Data Source=" + System.Environment.MachineName + "; " +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI;" +
                            "Application Name=testSPID;"+
                            "Connection Timeout=5");
            dbConnection.Open();
            loginTime = GetCurrentProcessLoginTime(dbConnection);

            try
            {
                task.DatabaseName = "master";
                task.Execute();
                if (GetCurrentProcessLoginTime(dbConnection) == loginTime)
                {
                    Assert.Fail("Login still exists, with identical login time");
                }
            }
            finally
            {
                if (dbConnection.State == ConnectionState.Open)
                  dbConnection.Close();
            }

        }

        /// <summary>
        /// Test Disconnect with no permission to do so
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestDisconnectUsersWithNoPermissions()
        {
            Guid guid = Guid.NewGuid();
            string username = guid.ToString();
            const string password = "1235$abcde";
            username = username.Replace("-", "").Substring(0, 11);

            Console.WriteLine("Test Setup, creating test username " + username);

            if (User.Exists(username))
                Assert.Fail("Temporary user already exists.");
            // Now create the user, using the CreateActiveDirectoryUser task
            Tasks.ActiveDirectory.User.Create adTask = new Tasks.ActiveDirectory.User.Create();
            adTask.UserName = username;
            adTask.Password = password;
            bool taskReturnValue = adTask.Execute();
            Assert.IsTrue(taskReturnValue, "Cannot create temporary user.");
            // Check the user now exists
            bool userExists = User.Exists(username);
            Assert.IsTrue(userExists, "Temporary user existance check.");

            // Now grant this user login rights to SQL
            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand grantLogin = new SqlCommand("sp_grantlogin '" +
                System.Environment.MachineName + @"\" + username + "'",
                dbConnection);
            SqlParameter grantSQLResult = grantLogin.Parameters.Add("@ReturnValue", SqlDbType.Int);
            grantSQLResult.Direction = ParameterDirection.ReturnValue;
            grantLogin.ExecuteNonQuery();
            int grantResult = (int) grantLogin.Parameters["@ReturnValue"].Value;
            grantLogin.Dispose();

            // And add it into the database
            SqlCommand grantDBAccess = new SqlCommand("sp_grantdbaccess '" +
                System.Environment.MachineName + @"\" + username + "','" +
                System.Environment.MachineName + @"\" + username + "'",
                dbConnection);
            SqlParameter grantDBResult = grantDBAccess.Parameters.Add("@ReturnValue", SqlDbType.Int);
            grantDBResult.Direction = ParameterDirection.ReturnValue;
            grantDBAccess.ExecuteNonQuery();
            int grantAccessResult = (int) grantDBAccess.Parameters["@ReturnValue"].Value;
            grantDBAccess.Dispose();

            Assert.AreEqual(0, grantResult);
            Assert.AreEqual( 0, grantAccessResult);
            dbConnection.Close();


            SqlConnection dbTempConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=tempdb;" +
                            "Integrated Security=SSPI;"+
                            "Application Name=holdingTemp" +
                            "Connection Timeout=5");
            dbTempConnection.Open();


            const int LOGON32_LOGON_INTERACTIVE = 2;
            const int LOGON32_PROVIDER_DEFAULT = 0;
            IntPtr userHandle = new IntPtr(0);
            bool returnValue = NativeMethods.LogonUser(username,
                System.Environment.MachineName,
                password,
                LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref userHandle);

            if (!returnValue)
                throw new Exception("Invalid Username");
            WindowsIdentity newId = new WindowsIdentity(userHandle);
            WindowsImpersonationContext impersonatedUser = newId.Impersonate();
            Console.WriteLine("Now running in context of : " +
                            WindowsIdentity.GetCurrent().Name);
            Tasks.Sql.DisconnectUsers contextTask = new Tasks.Sql.DisconnectUsers();;
            
            try
            {
                contextTask.DatabaseName = this.testDBName;
                contextTask.Execute();
            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine)contextTask.BuildEngine).PreviousMessage;
                // Make sure the error message was the correct no permissions
                Assert.IsTrue(lastMessage.IndexOf("User does not have permission to use the KILL statement.") >= 0);
            }
            finally
            {
                try
                {
                    impersonatedUser.Undo();
                    if (userHandle != IntPtr.Zero)
                        NativeMethods.CloseHandle(userHandle);
                }
                catch
                {
                    throw;
                }

                dbTempConnection.Close();
                dbConnection.Open();
                // Revoke the temporary username's access to SQL
                SqlCommand revokeLogin = new SqlCommand("sp_revokelogin '" +
                    System.Environment.MachineName + @"\" + username + "'",
                    dbConnection);
                SqlParameter revokeReturnValue = revokeLogin.Parameters.Add("@ReturnValue", SqlDbType.Int);
                revokeReturnValue.Direction = ParameterDirection.ReturnValue;
                revokeLogin.ExecuteNonQuery();
                int revokeResult = (int) revokeLogin.Parameters["@ReturnValue"].Value;
                revokeLogin.Dispose();
                dbConnection.Close();
                dbConnection.Dispose();


                User user = User.Load(username);
                user.Delete();
                userExists = User.Exists(username);
                Assert.IsFalse(userExists, "Temporary user could not be deleted");
                Assert.AreEqual( 0, revokeResult);
            }
        }


        /// <summary>
        /// Test Disconnect with no permission to do so
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestDisconnectUsersWithNoDBLoginPermission()
        {
            Guid guid = Guid.NewGuid();
            string username = guid.ToString();
            const string password = "1235$abcde";
            username = username.Replace("-", "").Substring(0, 11);

            Console.WriteLine("Test Setup, creating test username " + username);

            if (User.Exists(username))
                Assert.Fail("Temporary user already exists.");
            // Now create the user, using the CreateActiveDirectoryUser task
            Tasks.ActiveDirectory.User.Create adTask = new Tasks.ActiveDirectory.User.Create();
            adTask.UserName = username;
            adTask.Password = password;
            bool taskReturnValue = adTask.Execute();
            Assert.IsTrue(taskReturnValue, "Cannot create temporary user.");
            // Check the user now exists
            bool userExists = User.Exists(username);
            Assert.IsTrue(userExists, "Temporary user existance check.");

            const int LOGON32_LOGON_INTERACTIVE = 2;
            const int LOGON32_PROVIDER_DEFAULT = 0;
            IntPtr userHandle = new IntPtr(0);
            bool returnValue = NativeMethods.LogonUser(username,
                System.Environment.MachineName,
                password,
                LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref userHandle);

            if (!returnValue)
                throw new Exception("Invalid Username");
            WindowsIdentity newId = new WindowsIdentity(userHandle);
            WindowsImpersonationContext impersonatedUser = newId.Impersonate();
            Console.WriteLine("Now running in context of : " +
                            WindowsIdentity.GetCurrent().Name);
            
            Tasks.Sql.DisconnectUsers contextTask = new Tasks.Sql.DisconnectUsers();
                
            try
            {
               
                contextTask.DatabaseName = this.testDBName;
                contextTask.Execute();
            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine) contextTask.BuildEngine).PreviousMessage;
                // Make sure the error message was the correct no permissions
                Assert.IsTrue(lastMessage.IndexOf("Login failed for user '"+
                    System.Environment.MachineName + @"\" + username + "'") >= 0);
            }
            finally
            {
                try
                {
                    impersonatedUser.Undo();
                    if (userHandle != IntPtr.Zero)
                        NativeMethods.CloseHandle(userHandle);
                    User user = User.Load(username);
                    user.Delete();
                    userExists = User.Exists(username);
                    Assert.IsFalse(userExists, "Temporary user could not be deleted");
                }
                catch
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// Function to get the login time for the passed connection
        /// </summary>
        /// <param name="dbConnection">database connection</param>
        /// <returns>Connection's login time</returns>
        private DateTime GetCurrentProcessLoginTime(SqlConnection dbConnection)
        {
            DateTime loginTimeStamp = new DateTime();
            SqlCommand getLoginTimeStamp = new SqlCommand("select login_time from master..sysprocesses where spid=@@spid",
                dbConnection);
            SqlDataReader loginTimeStampResults = getLoginTimeStamp.ExecuteReader();
            loginTimeStampResults.Read();
            loginTimeStamp = (DateTime) loginTimeStampResults["login_time"];
            loginTimeStampResults.Close();
            loginTimeStampResults.Dispose();
            getLoginTimeStamp.Dispose();
            return (loginTimeStamp);
        }

        internal class NativeMethods
        {
            [DllImport("advapi32.dll", CharSet = CharSet.Auto)]
            public static extern bool LogonUser(String lpszUserName, String lpszDomain, String lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);
            [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
            public extern static bool CloseHandle(IntPtr handle);
        }
    }
}